﻿Create PROCEDURE [dbo].[se_GetAllSearchedUrl]
AS
BEGIN
      Select url from se_SearchData
END 


CREATE PROCEDURE [dbo].[se_InsertUrl]
      @Url varchar(1000),
      @Title varchar(500),
      @HtmlBodyText varchar(800)
AS
 declare @count numeric(18, 0)
BEGIN
      select @count=count(*) from se_SearchData where Url = @Url;
    if(@count = 0)      Insert into se_SearchData(Url, Title, HtmlBodyText) values(@Url, @Title, @HtmlBodyText)
    else update se_SearchData set Title = @Title, HtmlBodyText = @HtmlBodyText where Url = @Url;
END


CREATE PROCEDURE [dbo].[se_GetSearchDataByUrl]
      @Url varchar(500)
AS
BEGIN
      select * from se_SearchData where Url = @Url
END


CREATE PROCEDURE [dbo].[se_GetResultByKwPg]
	@Keyword varchar(100),
	@Page numeric(18, 0)
AS
BEGIN

Select * from	
	(select *, row_number() over(order by priority) as Row from
		(select *, 1 as priority from se_searchData where title like '%' + @Keyword + '%'
			union
		 select *, 2 as priority from se_SearchData where htmlBodyText like '%' + @Keyword + '%'
		) A)T
	where T.Row >=(@Page-1)*20  and T.Row <= @Page*20
END


CREATE PROCEDURE [dbo].[se_GetAllUrl]
AS
BEGIN
	SELECT distinct * from se_Url where inuse = 'T'
END